<?php
class Model_data extends Model {
    function Model_data() {
        parent::Model();
        $this->fromSession = $this->usersession->currentUser();
    }


    /**
     * Functia returneaza un json necesar pentru a fi transmis in browser . Acesta va fi folosit la constructia unor store-uri de comboboxuri ( orase, judete,etc )
     * Array PHP arata de genul
     * Array
    (
    [0] => Array
    (
    [0] => 2414
    [1] => 1 Decembrie
    [2] => 1 Decembrie (12)
    )
    ...
    )
    unde primul el. al fiecarui subarray este id, al 2-lea este denumirea si al 3 este denumire + nr de statistica ( cate contacte apartin de acel subiect)
    -ca statistica se aduc doar contactele care sunt disponibile
     **/

    function bringExt($table, $level = 0) {
        $array = array();

        $this->db->select($table . '_id as optionValue,name as optionDisplay');
        $this->db->where('level', $level);
        $this->db->order_by("name", "asc");
        $query = $this->db->get($table)->result();

        $column = $table . "_id";
        if ($level == '1') {
            $column = $table . "_sub_id";
        }

        //fac un query astfel incat sa obtin numarul de contactele disponibile grupate dupa subiect
        $countEach = $this->db->query("select count(person_id) as nr,{$column} from persons where available='1' group by {$column}")->result_array();
        foreach ($countEach as $row) {
            //fac un array de genul  x_id=> nr
            $countArray[$row[$column]] = $row['nr'];
        }

        //$query are forma : id, denumire
        //$countArray are forma : id_c, numar_c
        // pentru fiecare inregistrare din $query  caut sa vad daca id din se gaseste in array-ul $countArray in id_c

        foreach ($query as $key => $row) {

           $count = 0;
            if (array_key_exists($row->optionValue, $countArray)) {
                $count = $countArray[$row->optionValue];
            }

            //inlocuiesc ghilimelele cu coduri html pentru a nu avea probleme de decodarea json-ului in browser 
            //$row->optionDisplay = str_replace("'", "&#039;", $row->optionDisplay);
            //$row->optionDisplay = str_replace('"', "&#039;", $row->optionDisplay);

            $array[$key][] = $row->optionValue;
            $array[$key][] = $row->optionDisplay;
            $array[$key][] = $row->optionDisplay . " ({$count})";
        }
        return json_encode($array);
    }


    /**
     * return an Ext object uset in comboboxes , 1 row - id , 2 row - name , 3 - name with statistics get from persons table
     * @param string $field - field name where to get the information
     **/
    function bringExtCustom($field) {


        $aAvailable = $this->config->item("availableContact");
        $aPriority = $this->config->item("priorityContact");
        $aSex = $this->config->item("sexContact");

	    $aZona = $this->config->item("zonaAcoperireContact");
	    $aRate = $this->config->item("rateCardContact");
	    $aPeriodicitate = $this->config->item("periodicitateContact");


        switch ($field) {
            case('sex'):
                {
                $aBase = $aSex;
                $field = 'sex';
                $fieldSearch = "where available=1";
                }
                break;
            case('available'):
                {
                $aBase = $aAvailable;
                $field = 'available';
                $fieldSearch = "";
                }
                break;
            case('unsubscribed'):
                {
                $aBase = $aAvailable;
                $field = 'unsubscribed';
                $fieldSearch = "where available=1";
                }
                break;
            case('priority'):
                {
                $aBase = $aPriority;
                $field = 'priority';
                $fieldSearch = "where available=1";
                }
                break;
	        case('zona'):
	        {
		        $aBase = $aZona;
		        $field = 'zona';
		        $fieldSearch = "where available=1";
	        }
		        break;
	        case('rate'):
	        {
		        $aBase = $aRate;
		        $field = 'rate';
		        $fieldSearch = "where available=1";
	        }
		        break;
	        case('periodicitate'):
	        {
		        $aBase = $aPeriodicitate;
		        $field = 'periodicitate';
		        $fieldSearch = "where available=1";
	        }
		        break;
        }

        $countEach = $this->db->query("select count(person_id) as nr,{$field} from persons {$fieldSearch}  group by {$field}")->result_array();
        foreach ($countEach as $row) {
            //fac un array de genul  city_id=> nr
            $countArray[$row[$field]] = $row['nr'];
        }

        $i = 0;
        $array = array();

        //pentru fiecare cheie din $aBase caut sa vad daca sunt inregistrari in $countArray;
        foreach ($aBase as $key => $data) {
            if (array_key_exists($key, $countArray)) {
                $count = $countArray[$key];
            }
            else {
                $count = 0;
            }

            $array[$i][] = $key;
            $array[$i][] = $data;
            $array[$i][] = $data . " ({$count})";
            $i++;
        }
        return json_encode($array);
    }


    /**
     * Functie care aduce toti userii.. cei inactivi vor fi in inregistrarea ZOTHER
     */

    function bringUser() {

        $array = array();
        $this->db->flush_cache();
        $this->db->select('user_id as optionValue,name as optionDisplay', false);
        $this->db->where('available', '1');
        $this->db->order_by("name", "asc");
        $query = $this->db->get('user')->result();
        $i = 1;

        $array[0][] = "0";
        $array[0][] = "ZOTHER";
        foreach ($query as $row) {
            //$row->optionDisplay = str_replace("'", "&#039;", $row->optionDisplay);
            //$row->optionDisplay = str_replace('"', "&#039;", $row->optionDisplay);
            $array[$i][] = $row->optionValue;
            $array[$i][] = $row->optionDisplay;
            $i++;
        }
        return json_encode($array);
    }

    /**
     * Functie care aduce literele alfabetului
     */
    function bringLetters() {
        $array = array();
        $array[0][] = '-';
        $array[0][] = 'Restul';
        $j = 1;
        foreach (range('A', 'Z') as $i) {
            $array[$j][] = $i;
            $array[$j][] = $i;
            $j++;
        }
        return json_encode($array);
    }


    ////////////////////////
    /* set the user details */
    ////////////////////////////


    /**
     * functie de adaugare generala din meniu Operatii
     */

    function addObject($data, $table, $data_parent) {

        $this->db->where("{$table}.name", $data);
        $dataInsert = array('name' => strTitle($data), 'created_by' => $this->fromSession["user_id"]);

        if ($data_parent != '') {
            $dataInsert['parent_id'] = $data_parent;
            $dataInsert['level'] = 1;
            $this->db->where("{$table}.parent_id =", $data_parent);
            $this->db->where("{$table}.{$table}_id !=", $data_parent);
        }

        if ((($table == 'domain') || ($table == 'position')) && ($data_parent == '')) {
            $this->db->where("parent_id ={$table}_id", null, false);
        }

        $query = $this->db->get($table);

        if ($query->num_rows() > 0) {
            return false;
        }
        else {
            $this->db->flush_cache();
            $this->db->insert($table, $dataInsert);

            //daca se inseseara obiectul parinte, acesta va avea setat parent_id chiar id-ul lui
            if ((($table == 'domain') || ($table == 'position')) && ($data_parent == '')) {
                $id = $this->db->insert_id();
                $this->db->flush_cache();
                $this->db->where($table . '_id', $id);
                $this->db->set('parent_id', $id);
                $this->db->update($table);
            }
        }
        return true;
    }


    /**
     * Functie care aduce inregistrari folosita in meniul Operatii
     */
    function bringObject($data) {
        $this->db->flush_cache();

        $this->db->select($data['table'] . "_id as id, name", false);

        if ($data['data_parent'] != '') {

            $aDataParent = explode("=", $data['data_parent']);
            $this->db->where_in("parent_id", $aDataParent);
            $this->db->where($data['table'] . "_id !=", $data['data_parent']);
            /*
            $this->db->where("parent_id", $data['data_parent']);
            $this->db->where($data['table'] . "_id !=", $data['data_parent']); */
        }

        if ((($data['table'] == 'domain') || ($data['table'] == 'position')) && ($data['data_parent'] == '')) {
            $this->db->where("parent_id =" . $data['table'] . "_id", null, false);
        }

        $this->db->order_by($data['table'] . '.name', 'asc');
        $this->db->like('name', $data['query']);
        return $this->db->get($data['table'])->result_array();
    }

    /**
     * Functie care aduce domeniul - concatenat cu subdomeniul. In plus se aduce si domeniul simplu ( fara concatenare de subdomeniu ) pentru cazul in care se doreste doar completarea domeniului
     */
    function bringSubdomain($data) {
        //se selecteaza obiectele simple si apoi se da UNION cu subobiectele asociate
        $query = "
    (
    select CONCAT(domain_id, '-') as id, CONCAT(domain.name,' - ') as name, domain.name as name2
        from domain
        where name like '%" . addslashes($data["query"]) . "%'
    )    
    UNION
    (
    select CONCAT(P.domain_id,'-',domain.domain_id ) as id, CONCAT(P.name,' - ',domain.name),P.name as name2
        from domain
        left join domain P on P.domain_id=domain.parent_id    
        where domain.level=1 and CONCAT (domain.name,' - ',P.name ) like '%" . addslashes($data["query"]) . "%'
    )
    order by name asc";

        $result = $this->db->query($query)->result();
        return $result;
    }

    /* functie folosita la import */
    /* aduc toate obiectele */
    function bringAllObject($data) {

        $this->db->select($data['table'] . "_id as id, name", false);

        if ((($data['table'] == 'domain') || ($data['table'] == 'position')) && ($data['level'] == '0')) {
            $this->db->where("level", "0");
        }
        if ((($data['table'] == 'domain') || ($data['table'] == 'position')) && ($data['level'] == '1')) {
            $this->db->where("level", "1");
            $this->db->select("parent_id");
        }

        $this->db->order_by($data['table'] . '.name', 'asc');
        $result = $this->db->get($data['table'])->result_array();
        return $result;
    }


    function bringCompany($data) {
        $result = "";
        if (($data['query'] != '') && (strlen($data['query']) >= 3)) {
            $this->db->select("  distinct(company) as name ", false);
            $this->db->order_by('company', 'asc');
            $this->db->like('company', $data['query']);
            $result = $this->db->get("persons")->result_array();
        }
        return $result;
    }


    /*bringObject*/
    /* function bringMultiObject($data) {
      $this->db->select($data['table'] . "_id as id, name", false);

      if ($data['data_parent'] != '') {
          $aDataParent = explode("=", $data['data_parent']);
          $this->db->where_in("parent_id", $aDataParent);
          $this->db->where($data['table'] . "_id !=", $data['data_parent']);
      }

      if ((($data['table'] == 'domain') || ($data['table'] == 'position')) && ($data['data_parent'] == '')) {
          $this->db->where("parent_id =" . $data['table'] . "_id", null, false);
      }

      $this->db->order_by($data['table'] . '.name', 'asc');
      $this->db->like('name', $data['query']);
      $result = $this->db->get($data['table'])->result_array();
      return $result;
  }  */

    /*TOP */
    /*function bringTop($table, $s_limit, $e_limit, $sort, $dir) {

        $sQuery = "select count(person_id)as total,modify_by from persons where date(date_modified)=date(now())group by modify_by";
        $query = $this->db->query($sQuery);

        $result_mod = $query->result_array();

        $sQuery = "select count(person_id) as total,create_by from persons where date(date_created)=date(now()) group by create_by";
        $query = $this->db->query($sQuery);

        $result_create = $query->result_array();

        $this->db->select("name,user_id");
        $this->db->where("available", "1");
        $this->db->not_like("name", "petrovan");
        $query = $this->db->get("user");
        $result_user = $query->result_array();
        $totalCount = $this->db->count_all_results();

        foreach ($result_user as $key => $data) {
            $total = 0;
            foreach ($result_mod as $mod_data) {
                if ($mod_data["modify_by"] == $data["user_id"]) {
                    $result_user[$key]["mod"] = $mod_data["total"];
                    $total += $mod_data["total"];
                }
            }
            foreach ($result_create as $create_data) {
                if ($create_data["create_by"] == $data["user_id"]) {
                    $result_user[$key]["create"] = $create_data["total"];
                    $total += $create_data["total"];
                }
            }
            $result_user[$key]["total"] = $total;
        }

        $total = array();
        foreach ($result_user as $key => $row) {
            if ($row['total'] == 0) {
                unset($result_user[$key]);
                continue;
            }
            $total[$key] = $row['total'];
        }
        array_multisort($total, SORT_DESC, $result_user);

        $data = array("error" => false,
                      "type" => "silent",
                      'totalCount' => $totalCount,
                      'DS_OBJECT' => $result_user
        );
        return $data;
    }  */
}
//end model